const express = require('express');
const router = express.Router();
const pool = require('../config/db');
const { logOperation } = require('./logs');

// 状态码定义
const CODE = {
  SUCCESS: 0,
  PARAM_ERROR: 1001,
  NOT_FOUND: 1002,
  SERVER_ERROR: 500
};

/**
 * 获取性能概览数据
 * GET /api/performance/overview
 */
router.get('/overview', async (req, res) => {
  try {
    const connection = await pool.getConnection();
    try {
      // 1. 获取项目完成情况统计
      const [projectStats] = await connection.execute(`
        SELECT 
          COUNT(*) as total_projects,
          SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_projects,
          SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as ongoing_projects,
          SUM(CASE WHEN status = 'delayed' THEN 1 ELSE 0 END) as delayed_projects
        FROM projects
      `);

      // 2. 获取模块完成情况统计
      const [moduleStats] = await connection.execute(`
        SELECT 
          COUNT(*) as total_modules,
          SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) as completed_modules,
          SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as ongoing_modules,
          SUM(CASE WHEN status = 'delayed' THEN 1 ELSE 0 END) as delayed_modules
        FROM modules
      `);

      // 3. 获取Bug修复情况统计
      const [bugStats] = await connection.execute(`
        SELECT 
          COUNT(*) as total_bugs,
          SUM(CASE WHEN status = 'resolved' THEN 1 ELSE 0 END) as resolved_bugs,
          SUM(CASE WHEN status = 'in_progress' THEN 1 ELSE 0 END) as processing_bugs,
          SUM(CASE WHEN priority = 'high' AND status != 'resolved' THEN 1 ELSE 0 END) as high_priority_bugs
        FROM bugs
      `);

      // 4. 获取近30天每日完成的任务数量趋势
      const [dailyCompletion] = await connection.execute(`
        SELECT 
          DATE(completion_date) as date,
          COUNT(*) as completed_count
        FROM (
          SELECT completion_date FROM projects WHERE status = 'completed' AND completion_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
          UNION ALL
          SELECT completion_date FROM modules WHERE status = 'completed' AND completion_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
          UNION ALL
          SELECT resolution_date as completion_date FROM bugs WHERE status = 'resolved' AND resolution_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
        ) as completions
        GROUP BY DATE(completion_date)
        ORDER BY date
      `);

      // 5. 获取团队效率指标
      const [teamEfficiency] = await connection.execute(`
        SELECT 
          AVG(TIMESTAMPDIFF(DAY, start_date, IFNULL(completion_date, CURDATE()))) as avg_project_duration,
          AVG(TIMESTAMPDIFF(HOUR, start_date, IFNULL(resolution_date, NOW()))) as avg_bug_resolution_time
        FROM (
          SELECT start_date, completion_date FROM projects
          UNION ALL
          SELECT start_date as start_date, completion_date FROM modules
        ) as durations
        CROSS JOIN (
          SELECT create_time, resolution_date FROM bugs WHERE status = 'resolved'
        ) as bug_durations
      `);

      // 6. 获取资源利用率
      const [resourceUtilization] = await connection.execute(`
        SELECT 
          u.id,
          u.username,
          COUNT(DISTINCT p.id) as assigned_projects,
          COUNT(DISTINCT m.id) as assigned_modules,
          COUNT(DISTINCT b.id) as assigned_bugs,
          ROUND(
            (COUNT(DISTINCT p.id) + COUNT(DISTINCT m.id) + COUNT(DISTINCT b.id)) / 
            (SELECT COUNT(*) FROM users WHERE role_id != 1) * 100, 
            2
          ) as utilization_rate
        FROM users u
        LEFT JOIN project_members pm ON u.id = pm.user_id
        LEFT JOIN projects p ON pm.project_id = p.id
        LEFT JOIN module_assignments ma ON u.id = ma.user_id
        LEFT JOIN modules m ON ma.module_id = m.id
        LEFT JOIN bugs b ON u.id = b.assignee_id
        WHERE u.role_id != 1
        GROUP BY u.id
        ORDER BY utilization_rate DESC
        LIMIT 5
      `);

      res.json({
        code: CODE.SUCCESS,
        message: '获取性能概览数据成功',
        data: {
          project_statistics: projectStats[0],
          module_statistics: moduleStats[0],
          bug_statistics: bugStats[0],
          daily_completion_trend: dailyCompletion,
          team_efficiency: teamEfficiency[0],
          resource_utilization: resourceUtilization
        }
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('获取性能概览数据错误:', error);
    res.status(500).json({
      code: CODE.SERVER_ERROR,
      message: '服务器错误'
    });
  }
});

/**
 * 获取成员洞察数据
 * GET /api/performance/member-insights
 */
router.get('/member-insights', async (req, res) => {
  try {
    const connection = await pool.getConnection();
    try {
      // 1. 获取成员基本工作量统计
      const [workloadStats] = await connection.execute(`
        SELECT 
          u.id,
          u.username,
          u.real_name,
          u.department,
          COUNT(DISTINCT p.id) as project_count,
          COUNT(DISTINCT m.id) as module_count,
          COUNT(DISTINCT b.id) as bug_count,
          COUNT(DISTINCT CASE WHEN p.status = 'completed' THEN p.id END) as completed_projects,
          COUNT(DISTINCT CASE WHEN m.status = 'completed' THEN m.id END) as completed_modules,
          COUNT(DISTINCT CASE WHEN b.status = 'resolved' THEN b.id END) as resolved_bugs
        FROM users u
        LEFT JOIN project_members pm ON u.id = pm.user_id
        LEFT JOIN projects p ON pm.project_id = p.id
        LEFT JOIN module_assignments ma ON u.id = ma.user_id
        LEFT JOIN modules m ON ma.module_id = m.id
        LEFT JOIN bugs b ON u.id = b.assignee_id
        WHERE u.role_id != 1
        GROUP BY u.id
      `);

      // 2. 获取成员效率指标
      const [efficiencyStats] = await connection.execute(`
        SELECT 
          u.id,
          AVG(TIMESTAMPDIFF(DAY, p.start_date, p.completion_date)) as avg_project_completion_days,
          AVG(TIMESTAMPDIFF(DAY, m.start_date, m.completion_date)) as avg_module_completion_days,
          AVG(TIMESTAMPDIFF(HOUR, b.create_time, b.resolution_date)) as avg_bug_resolution_hours
        FROM users u
        LEFT JOIN project_members pm ON u.id = pm.user_id
        LEFT JOIN projects p ON pm.project_id = p.id AND p.status = 'completed'
        LEFT JOIN module_assignments ma ON u.id = ma.user_id
        LEFT JOIN modules m ON ma.module_id = m.id AND m.status = 'completed'
        LEFT JOIN bugs b ON u.id = b.assignee_id AND b.status = 'resolved'
        WHERE u.role_id != 1
        GROUP BY u.id
      `);

      // 3. 获取成员质量指标
      const [qualityStats] = await connection.execute(`
        SELECT 
          u.id,
          COUNT(DISTINCT CASE WHEN b.severity = 'critical' THEN b.id END) as critical_bugs,
          COUNT(DISTINCT CASE WHEN b.severity = 'high' THEN b.id END) as high_bugs,
          COUNT(DISTINCT CASE WHEN b.reopen_count > 0 THEN b.id END) as reopened_bugs,
          ROUND(
            COUNT(DISTINCT CASE WHEN b.status = 'resolved' AND b.reopen_count = 0 THEN b.id END) * 100.0 / 
            NULLIF(COUNT(DISTINCT b.id), 0),
            2
          ) as first_time_resolution_rate
        FROM users u
        LEFT JOIN bugs b ON u.id = b.assignee_id
        WHERE u.role_id != 1
        GROUP BY u.id
      `);

      // 4. 获取成员协作指标
      const [collaborationStats] = await connection.execute(`
        SELECT 
          u.id,
          COUNT(DISTINCT CASE WHEN c.comment_type = 'comment' THEN c.id END) as comment_count,
          COUNT(DISTINCT CASE WHEN c.comment_type = 'review' THEN c.id END) as review_count,
          COUNT(DISTINCT pm2.user_id) as collaborator_count
        FROM users u
        LEFT JOIN bug_comments c ON u.id = c.user_id
        LEFT JOIN project_members pm ON u.id = pm.user_id
        LEFT JOIN project_members pm2 ON pm.project_id = pm2.project_id AND pm2.user_id != u.id
        WHERE u.role_id != 1
        GROUP BY u.id
      `);

      // 5. 获取成员成长趋势（近6个月）
      const [growthTrend] = await connection.execute(`
        SELECT 
          u.id,
          DATE_FORMAT(completion_date, '%Y-%m') as month,
          COUNT(*) as completed_tasks
        FROM users u
        LEFT JOIN (
          SELECT pm.user_id, p.completion_date 
          FROM project_members pm 
          JOIN projects p ON pm.project_id = p.id 
          WHERE p.status = 'completed'
          UNION ALL
          SELECT ma.user_id, m.completion_date
          FROM module_assignments ma
          JOIN modules m ON ma.module_id = m.id
          WHERE m.status = 'completed'
          UNION ALL
          SELECT assignee_id as user_id, resolution_date as completion_date
          FROM bugs
          WHERE status = 'resolved'
        ) completions ON u.id = completions.user_id
        WHERE u.role_id != 1
          AND completion_date >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
        GROUP BY u.id, month
        ORDER BY month
      `);

      // 合并所有统计数据
      const memberInsights = workloadStats.map(member => ({
        id: member.id,
        username: member.username,
        real_name: member.real_name,
        department: member.department,
        workload: {
          project_count: member.project_count,
          module_count: member.module_count,
          bug_count: member.bug_count,
          completed_projects: member.completed_projects,
          completed_modules: member.completed_modules,
          resolved_bugs: member.resolved_bugs
        },
        efficiency: {
          avg_project_completion_days: efficiencyStats.find(e => e.id === member.id)?.avg_project_completion_days || 0,
          avg_module_completion_days: efficiencyStats.find(e => e.id === member.id)?.avg_module_completion_days || 0,
          avg_bug_resolution_hours: efficiencyStats.find(e => e.id === member.id)?.avg_bug_resolution_hours || 0
        },
        quality: {
          critical_bugs: qualityStats.find(q => q.id === member.id)?.critical_bugs || 0,
          high_bugs: qualityStats.find(q => q.id === member.id)?.high_bugs || 0,
          reopened_bugs: qualityStats.find(q => q.id === member.id)?.reopened_bugs || 0,
          first_time_resolution_rate: qualityStats.find(q => q.id === member.id)?.first_time_resolution_rate || 0
        },
        collaboration: {
          comment_count: collaborationStats.find(c => c.id === member.id)?.comment_count || 0,
          review_count: collaborationStats.find(c => c.id === member.id)?.review_count || 0,
          collaborator_count: collaborationStats.find(c => c.id === member.id)?.collaborator_count || 0
        },
        growth_trend: growthTrend.filter(g => g.id === member.id).map(g => ({
          month: g.month,
          completed_tasks: g.completed_tasks
        }))
      }));

      res.json({
        code: CODE.SUCCESS,
        message: '获取成员洞察数据成功',
        data: memberInsights
      });
    } finally {
      connection.release();
    }
  } catch (error) {
    console.error('获取成员洞察数据错误:', error);
    res.status(500).json({
      code: CODE.SERVER_ERROR,
      message: '服务器错误'
    });
  }
});

// 获取项目进度-模拟进度数据
router.get("/project_progress", async (req, res) => { 
  try{
    let data = {
       xAxis:['一月','二月','三月','四月','五月','六月','七月','八月','九月','十月','十一月','十二月'],
       series:[
          {
             name:"计划完成",
             data:[120, 132, 101, 134, 90, 230, 210, 230, 120, 132, 101, 134]
          },
          // 实际完成
          {
             name:"实际完成",
             data:[220, 182, 191, 234, 290, 330, 310, 220, 182, 191, 234, 290]
          },
          // 任务数
          {
             name:"任务数",
             data:[150, 232, 201, 154, 190, 330, 410, 150, 232, 201, 154, 190]
          },
          // bug数
          {
             name:"bug数",
             data:[320, 332, 301, 334, 390, 330, 320, 332, 301, 334, 390, 330]
          },
          // 任务完成率
          {
             name:"任务完成率",
             data:[320, 332, 301, 334, 390, 330, 320, 332, 301, 334, 390, 330]
          }
       ]  
    }

    res.json({
      code: CODE.SUCCESS,
      message: '获取项目进度数据成功',
      data: data
    });

  }catch(error){
    
  }
})
module.exports = router;
